/**
 * Collaborative Story Development Web Application (CSDApp) 
 * Copyright Anton Strack 2014
 *
 * This file is part of Collaborative Story Development Application (CSDApp).
 *
 * CSDApp is free software: you can redistribute it and/or modify it under the
 * terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or any later version.
 *
 * CSDApp is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * CSDApp. If not, see <http://www.gnu.org/licenses/>.
 *
 */
package csdwa;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * This class provides database access to the specific resource model. It
 * provides CRUD database functionality and it stores it's results as a list.
 * Additionally, through it's methods, it provides access to determine the table
 * name, and query markers that are set for the particular DAO and thus allows
 * other code a means of interacting with this DAO to execute more advanced
 * queries.
 *
 * @author Anton Strack
 */
public class TaskDao extends DaoSupport implements CsdwaDao<Task> {

    private List<Task> beans;

    public TaskDao(Connection conn) {
        super(conn);
        this.beans = new ArrayList<Task>();
        this.restoreQueryMarkers();
    }

    /**
     * Select all the beans from the bean table
     *
     * @return the row count indicating if 0 or more beans were retrieved from
     * the database.
     */
    @Override
    public int selectBeans() {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "1=1");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(this.usingQueryMarkers, SELECT_QUERY));
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param id the id number of the bean to select.
     * @return 1 or 0, indicating a bean was retrieved or not.
     */
    @Override
    public int selectBeanById(int id) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "task.ID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, id);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param recipientId the id of the recipient user.
     * @return the number of tasks returned or zero if none were found.
     */
    public int selectTasksByRecipientId(int recipientId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "task.RecipientID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, recipientId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param postTypeId the id indicating the post type.
     * @return the number of tasks returned or zero if none were found.
     */
    public int selectTasksByPostTypeId(int postTypeId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "task.PostResourceTypeID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, postTypeId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param recipientId the id of the recipient user.
     * @param postTypeId the id indicating the post type.
     * @return the number of tasks returned or zero if none were found.
     */
    public int selectTasksByPostTypeId(int recipientId, int postTypeId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "task.RecipientID = ? AND task.PostResourceTypeID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, recipientId);
            this.ps.setInt(2, postTypeId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param recipientId the id of the recipient user.
     * @param stateId the id indicating the current state of the task towards
     * being completed.
     * @param equalingState should the tasks be selected that only equal the
     * stateId or all but those that equal the stateId
     * @return the number of tasks returned or zero if none were found.
     */
    public int selectTasksByStatus(int recipientId, int stateId, boolean equalingState) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        if (equalingState == true) {
            this.setMarker(QueryMarker.CORE_CONDITIONS, "task.RecipientID = ? AND task.StateID = ?");
        } else {
            this.setMarker(QueryMarker.CORE_CONDITIONS, "task.RecipientID = ? AND task.StateID <> ?");
        }
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, recipientId);
            this.ps.setInt(2, stateId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by it's id.
     *
     * @param creatorId the id of the recipient user.
     * @return the number of tasks returned or zero if none were found.
     */
    public int selectTasksByCreator(int creatorId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "task.CreatedByID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, creatorId);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Clear the bean list so it doesn't contain any beans.
     */
    @Override
    public void clearDaoBeans() {
        this.beans.clear();
    }

    /**
     * Get the bean stored in the dao by it's primary id.
     *
     * @param id the id of the bean to select.
     * @return the bean if it exists in the dao or null if it doesn't
     */
    @Override
    public Task getBeanById(int id) {
        Task bean = null;
        if (this.beans.isEmpty() == false) {
            for (Task b : this.beans) {
                if (b.getId() == id) {
                    bean = b;
                    break;
                }
            }
        }
        return bean;
    }

    /**
     * Save a bean to the database
     *
     * @param bean the bean to insert. The bean id is not required and won't be
     * used. All other bean values are required.
     * @return the auto-generated id key for the new bean row or 0 on failure.
     */
    @Override
    public int insertBean(Task bean) {
        int result = 0;
        ResultSet rs;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_COLUMNS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.usingQueryMarkers.add(QueryMarker.INSERT_VALUES);
        /*
         * HSQLDB cannot properly handle insert statements that have table identifiesr in the form of table.columnName so we need to remove the table name for inserts.
         */
        this.queryMarkers.put(QueryMarker.CORE_COLUMNS, this.prepareInsertColumns(this.getMarker(QueryMarker.CORE_COLUMNS)));
        try {
            this.ps = this.getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, INSERT_QUERY), Statement.RETURN_GENERATED_KEYS);
            this.ps.setNull(1, java.sql.Types.NULL);
            this.ps.setInt(2, bean.getUpdatedById());
            this.ps.setInt(3, bean.getCreatedById());
            this.ps.setInt(4, bean.getStateId());
            this.ps.setInt(5, bean.getResourceTypeId());
            this.ps.setInt(6, bean.getPostId());
            this.ps.setTimestamp(7, bean.getUpdatedDate());
            this.ps.setTimestamp(8, bean.getCreatedDate());
            this.ps.setString(9, bean.getName());
            this.ps.setString(10, bean.getDescription());
            this.ps.setInt(11, bean.getRecipientId());
            this.ps.setInt(12, bean.getPostResourceTypeId());
            this.ps.setTimestamp(13, bean.getStatusDate());
            this.ps.setInt(14, bean.getActionTypeId());
            result = this.ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (result != 0 && rs.next()) {
                result = rs.getInt(1); // get the auto-generated key from the successful insert operation.
            }
        } catch (SQLException ex) {
            Logger.getLogger(TaskDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Update a bean in the database as well as the bean in the dao's beans map.
     *
     * @param bean the bean to update.
     * @return 1 or 0 indicating if the bean was updated or not.
     */
    @Override
    public int updateBean(Task bean) {
        int result = 0;

        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_CONDITIONS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.usingQueryMarkers.add(QueryMarker.UPDATE_COLUMNS_VALUES);

        String[] cols = this.splitStringByCommas(this.COLUMNS_TO_UPDATE);

        this.setMarker(QueryMarker.UPDATE_COLUMNS_VALUES, this.prepareUpdateColumns(cols));
        this.setMarker(QueryMarker.CORE_CONDITIONS, "ID = ?");

        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, UPDATE_QUERY));
            int index = 1;
            for (String col : cols) {
                if ("name".equalsIgnoreCase(col)) {
                    this.ps.setString(index, bean.getName());
                    index++;
                } else if ("description".equalsIgnoreCase(col)) {
                    this.ps.setString(index, bean.getDescription());
                    index++;
                } else if ("postId".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getPostId());
                    index++;
                } else if ("PostResourceTypeId".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getPostResourceTypeId());
                    index++;
                } else if ("recipientID".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getRecipientId());
                    index++;
                } else if ("createdById".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getCreatedById());
                    index++;
                } else if ("updatedById".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getUpdatedById());
                    index++;
                } else if ("stateId".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getStateId());
                    index++;
                } else if ("actionTypeID".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getActionTypeId());
                    index++;
                } else if ("resourceTypeId".equalsIgnoreCase(col)) {
                    this.ps.setInt(index, bean.getResourceTypeId());
                    index++;
                } else if ("updatedDate".equalsIgnoreCase(col)) {
                    this.ps.setTimestamp(index, bean.getUpdatedDate());
                    index++;
                } else if ("createdDate".equalsIgnoreCase(col)) {
                    this.ps.setTimestamp(index, bean.getCreatedDate());
                    index++;
                } else if ("statusDate".equalsIgnoreCase(col)) {
                    this.ps.setTimestamp(index, bean.getStatusDate());
                    index++;
                }
            }
            this.ps.setInt(index, bean.getId()); // where column condition
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ResourceTypeDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Deletes the bean from the database
     *
     * @param bean the bean to delete
     * @return 1 or 0 indicating if the bean was deleted or not.
     */
    @Override
    public int deleteBean(Task bean) {
        int result = 0;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_CONDITIONS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.setMarker(QueryMarker.CORE_CONDITIONS, "ID = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, DELETE_QUERY));
            this.ps.setInt(1, bean.getId());
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(TaskDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Reset the query markers to their default values for the dao.
     */
    @Override
    public void restoreQueryMarkers() {
        this.setMarker(QueryMarker.CORE_TABLE, "task");
        this.setMarker(QueryMarker.CORE_COLUMNS, "task.ID, task.UpdatedByID, task.CreatedByID, task.StateID, task.ResourceTypeID, task.PostID, task.UpdatedDate, task.CreatedDate, task.Name, task.Description, task.RecipientID, task.PostResourceTypeID, task.StatusDate, task.ActionTypeID");
        this.setMarker(QueryMarker.CORE_CONDITIONS, "task.ID = ?");
        this.setMarker(QueryMarker.ORDER_BY_COLUMN, "task.UpdatedDate");
        this.setMarker(QueryMarker.ORDER_BY_MODE, "DESC");
        this.setMarker(QueryMarker.LIMIT, "");
        this.setMarker(QueryMarker.INSERT_VALUES, "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?");
        this.setMarker(QueryMarker.UPDATE_COLUMNS_VALUES, "");
        this.setMarker(QueryMarker.PRIMARY_KEY, "ID");
        // special type of derived values
        this.setMarker(QueryMarker.RELATED_COLUMNS, ", upUser.Username AS UpdatedByName, crUser.Username AS CreatedByName, rpUser.Username AS RecipientName, state.name AS StateName, taskaction.Name AS ActionTypeName, resourcetype.Name as PostResourceTypeName");
        this.setMarker(QueryMarker.RELATED_CONDITIONS, " AND task.UpdatedByID = upUser.ID AND task.CreatedByID = crUser.ID AND StateID = state.ID AND task.RecipientID = rpUser.ID AND task.ActionTypeID = taskaction.ID AND task.PostResourceTypeID = resourcetype.ID");
        this.setMarker(QueryMarker.RELATED_TABLES, ", useraccount AS upUser, useraccount AS crUser, useraccount AS rpUser, state, resourcetype, taskaction");
        this.COLUMNS_TO_UPDATE = "UpdatedByID, StateID, UpdatedDate, Description, StatusDate";
    }

    /**
     * Execute the select query and place the results in beans list.
     *
     * @param ps the fully prepared PreparedStatement
     * @return the row count from the select statement
     */
    @Override
    protected int executeSelectQuery(PreparedStatement ps) {
        int result = 0;
        ResultSet rs;
        try {
            rs = ps.executeQuery();
            while (rs.next()) {
                Task bean = ResourceFactory.makeTask();
                bean.setId(rs.getInt("task.ID"));//
                bean.setUpdatedById(rs.getInt("task.UpdatedByID"));
                bean.setCreatedById(rs.getInt("task.CreatedByID"));
                bean.setRecipientId(rs.getInt("task.RecipientID"));
                bean.setActionTypeId(rs.getInt("task.ActionTypeID"));
                bean.setStateId(rs.getInt("task.StateID"));
                bean.setResourceTypeId(rs.getInt("task.ResourceTypeID"));
                bean.setPostId(rs.getInt("task.PostID"));
                bean.setPostResourceTypeId(rs.getInt("task.PostResourceTypeID"));
                bean.setUpdatedDate(rs.getTimestamp("task.UpdatedDate"));
                bean.setCreatedDate(rs.getTimestamp("task.CreatedDate"));
                bean.setStatusDate(rs.getTimestamp("task.StatusDate"));
                bean.setName(rs.getString("task.Name"));
                bean.setDescription(rs.getString("task.Description"));
                if (this.USE_RELATED_MARKERS == true) {
                    bean.setDerivedValue("UpdatedByName", rs.getString("UpdatedByName"));
                    bean.setDerivedValue("CreatedByName", rs.getString("CreatedByName"));
                    bean.setDerivedValue("RecipientName", rs.getString("RecipientName"));
                    bean.setDerivedValue("ActionTypeName", rs.getString("ActionTypeName"));
                    bean.setDerivedValue("StateName", rs.getString("StateName"));
                    bean.setDerivedValue("PostResourceTypeName", rs.getString("PostResourceTypeName"));
                }
                this.processDerivedValues(bean, rs);
                this.beans.add(bean);
                result++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ActionDao.class.getName()).log(Level.SEVERE, null, ex);
        }


        return result;
    }

    /**
     * Get a list of beans
     *
     * @return a list of bean objects
     */
    @Override
    public List<Task> getBeans() {
        return new ArrayList<Task>(this.beans);
    }

    /**
     * Get a single bean.
     *
     * @return a single bean in the bean dao.
     */
    @Override
    public Task getBean() {
        if (this.beans.isEmpty()) {
            return null;
        }
        return this.beans.get(0);
    }

    /**
     * Add a bean in the dao's bean list.
     *
     * @param bean the bean to add or update
     */
    @Override
    public void addBeanToDao(Task bean) {
        this.beans.add(bean);
    }
}
